package com.winning.sjzc.xmgl.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.winning.sjzc.xmgl.dao.XmglDao;
import com.winning.sjzc.xmgl.entity.*;
import com.winning.sjzc.xmgl.service.XmglService;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @description: 项目管理
 * @author: jinx
 * @create: 2019-04-30
 **/
@Service("XmglServiceImpl")
public class XmglServiceImpl implements XmglService {

    @Autowired
    private XmglDao xmglDao;

    @Override
    public List<XmglEntity> queryXmglTableService(String xmmc, int startNum, int endNum) {
        Map<String,Object> queryMap = new HashMap<>();
        queryMap.put("xmmc", '%'+xmmc+'%');
        queryMap.put("startNum", startNum);
        queryMap.put("endNum", endNum);
        return xmglDao.queryXmglTableData(queryMap);
    }

    @Override
    public boolean queryIfXmExistService(String xmdm) {
        List<XmglEntity> xmglList = xmglDao.queryXmglByXmdm(xmdm);
        if(xmglList.size() > 0) return true;
        return false;
    }

    @Override
    public int insertXminfoService(String xmdm, String xmmc, String xmms) {
        XmglEntity xmglEntity = new XmglEntity();
        xmglEntity.setXmdm(xmdm);
        xmglEntity.setXmmc(xmmc);
        xmglEntity.setXmms(xmms);
        return xmglDao.insertXminfo(xmglEntity);
    }

    @Override
    public int updateXminfoService(String xmdm, String xmmc, String xmms) {
        XmglEntity xmglEntity = new XmglEntity();
        xmglEntity.setXmdm(xmdm);
        xmglEntity.setXmmc(xmmc);
        xmglEntity.setXmms(xmms);
        return xmglDao.updateXminfo(xmglEntity);
    }

    @Override
    public int delXminfoService(String xmdm) {
        return xmglDao.delXminfo(xmdm);
    }

    @Override
    public String getNewXmdmService() {
        return xmglDao.getNewXmdm();
    }

    @Override
    public List<XmglEntity> querySjjTableService(String sjjmc, String xmdm, String fldm, int startNum, int endNum) {
        Map<String,Object> queryMap = new HashMap<>();
        queryMap.put("xmdm", xmdm);
        queryMap.put("sjjmc", '%'+sjjmc+'%');
        queryMap.put("fldm", fldm);
        queryMap.put("startNum", startNum);
        queryMap.put("endNum", endNum);
        return xmglDao.getSjjTable(queryMap);
    }

    @Override
    public List<Object> querySjjflTreeService(String xmdm) {
        List<XmglEntity> sjjflList = xmglDao.getSjjflTree(xmdm);
        List<Object> resList = new LinkedList<>();
        for(int i=0; i<sjjflList.size(); i++){
            XmglEntity sjjflObj = sjjflList.get(i);
            String fldm = sjjflObj.getSjjfldm();
            String flmc = sjjflObj.getSjjflmc();
            String yjlbdm = sjjflObj.getYjlbdm();
            String ejlbdm = sjjflObj.getEjlbdm();
            String fljb = sjjflObj.getFljb();
            JSONObject jsonObj = new JSONObject();
            jsonObj.put("id", fldm);
            jsonObj.put("name", flmc);
            if("1".equals(fljb)){
                jsonObj.put("pId", "0");
            }else if("2".equals(fljb)){
                jsonObj.put("pId", yjlbdm);
            }else if("3".equals(fljb)){
                jsonObj.put("pId", ejlbdm);
            }
            resList.add(jsonObj);
        }
        return resList;
    }

    @Override
    public List<XmglEntity> querySjjImportTableService(String sjjmc, String xmdm, int startNum, int endNum) {
        Map<String,Object> queryMap = new HashMap<>();
        queryMap.put("xmdm", xmdm);
        queryMap.put("sjjmc", '%'+sjjmc+'%');
        queryMap.put("startNum", startNum);
        queryMap.put("endNum", endNum);
        return xmglDao.getSjjImportTable(queryMap);
    }

    @Override
    public int importSjjService(List<XmglEntity> importList) {
        return xmglDao.insertSjj(importList);
    }

    @Override
    public int delSjjService(String lsh) {
        return xmglDao.delSjj(lsh);
    }

    @Override
    public int updateVersionService(String xmdm) {
        return xmglDao.updateVersion(xmdm);
    }

    /**
     * 生成ETL脚本
     * @return
     */
    @Override
    public String createEtlScript(String xmdm) {
        /***************先根据项目标准，生成tran和job数据*************/

        //生成1级job信息
        //获取当前项目所有数据集的1级分类
        List<String> listFlxx= xmglDao.queryXmbzfl(xmdm);
        Map map = new HashMap();
        map.put("xmdm",xmdm);

        for (String sjjflmc:listFlxx) {
            //获取当前分类下的数据集列表
            map.put("sjjflmc",sjjflmc);
            List<SjjEntity> listSjj= xmglDao.queryXmbzSjj(map);
            //获取该分类的job信息
            List<JobEntity> listJobxx = xmglDao.queryJobByJobname(sjjflmc);
            for (JobEntity ls:listJobxx) {
                String xml = ls.getJobXml();
                try {
                    Document document = DocumentHelper.parseText(xml); // 将字符串转为XML
                    Element rootElt = document.getRootElement(); // 获取根节点
                    //获取根节点下的entries节点
                    Element entries = rootElt.element("entries");
                    List<Element> listEnry  = entries.elements("entry");
                    //先删除所有的enrry节点(除了START和“成功”)
                    for (Element elementEnry:listEnry) {
                        if(!elementEnry.element("name").getText().equals("START") &&
                        !elementEnry.element("name").getText().equals("成功")){
                            entries.remove(elementEnry);
                        }
                    }

                    //设置作业的跳
                    Element hops = rootElt.element("hops");
                    List<Element> listHops  = hops.elements("hop");
                    for (Element elementHop:listHops) {
                        hops.remove(elementHop);
                    }

                    //遍历所有的数据集
                    int y = 10;
                    for (SjjEntity sjj:listSjj) {
                        //1、创建entry节点
                        Element entry  = DocumentHelper.createElement("entry");
                        Element name = DocumentHelper.createElement("name");
                        name.setText(sjj.getSjkbm());
                        entry.add(name);
                        Element jobname = DocumentHelper.createElement("jobname");
                        jobname.setText(sjj.getSjkbm());
                        entry.add(jobname);
                        Element directory = DocumentHelper.createElement("directory");
                        if("1".equals(ls.getJobType())){
                            directory.setText("/前置机流程/业务流程/公共组件/"+sjjflmc+"/"+sjj.getSjkbm()+"");
                        }else{
                            directory.setText("/中心端流程/业务流程/公共组件/"+sjjflmc+"/"+sjj.getSjkbm()+"");
                        }
                        entry.add(directory);

                        Element type = DocumentHelper.createElement("type");
                        type.setText("JOB");
                        entry.add(type);

                        Element draw = DocumentHelper.createElement("draw");
                        draw.setText("Y");
                        entry.add(draw);

                        Element xloc = DocumentHelper.createElement("xloc");
                        xloc.setText("400");
                        entry.add(xloc);

                        Element yloc = DocumentHelper.createElement("yloc");
                        yloc.setText(String.valueOf(y));
                        entry.add(yloc);

                        listEnry.add(entry);  //将新增加的节点加到entries下面

                        //2、创建HOP节点
                        Element hop  = DocumentHelper.createElement("hop");
                        Element from = DocumentHelper.createElement("from");
                        from.setText("START");
                        hop.add(from);
                        Element to = DocumentHelper.createElement("to");
                        to.setText(sjj.getSjkbm());
                        hop.add(to);
                        Element unconditional = DocumentHelper.createElement("unconditional");
                        unconditional.setText("Y");
                        hop.add(unconditional);
                        listHops.add(hop);

                        Element hop1  = DocumentHelper.createElement("hop");
                        Element from1 = DocumentHelper.createElement("from");
                        from1.setText(sjj.getSjkbm());
                        hop1.add(from1);
                        Element to1 = DocumentHelper.createElement("to");
                        to1.setText("成功");
                        hop1.add(to1);
                        Element unconditional1 = DocumentHelper.createElement("unconditional");
                        unconditional1.setText("N");
                        hop1.add(unconditional1);
                        listHops.add(hop1);

                        y = y+100;
                    }
                    ls.setJobXml(document.asXML());
                } catch (DocumentException e) {
                    e.printStackTrace();
                }
            }
            xmglDao.updateJob(listJobxx);

            //生成2级job信息
            for (SjjEntity sjjEntity : listSjj) {
                String tbName =sjjEntity.getSjkbm(); //表名
                //生成2级job信息
                //判断job中是否已存在（模糊查询）
                List<JobEntity> listEjJobxx = xmglDao.queryJobByJobname(tbName);
                if(listEjJobxx!=null && listEjJobxx.size()>0){  //存在配置信息，则更新
                    for (JobEntity ls:listEjJobxx) {
                        ls.setStates("0");
                    }
                    xmglDao.updateJob(listEjJobxx);
                } else { //不存在配置信息，则新增2级job
                    //从其他的job复制一个过来
                    List<JobEntity> jobList =  xmglDao.queryOneJob();
                    for(JobEntity job : jobList){
                        //修改复制后的新job
                        job.setJobName(tbName + ".kjb");
                        if("1".equals(job.getJobType())){
                            job.setJobPath("前置机流程\\业务流程\\公共组件\\" + sjjflmc + "\\" + tbName + "\\");
                        }else{
                            job.setJobPath("中心端流程\\业务流程\\公共组件\\" + sjjflmc + "\\" + tbName + "\\");
                        }
                        String xml = job.getJobXml();

                        //解析xml，并更新为新表的XML
                        try {
                            Document document = DocumentHelper.parseText(xml); // 将字符串转为XML
                            Element rootElt = document.getRootElement(); // 获取根节点
                            Element name = rootElt.element("name");
                            name.setText(tbName);
                            Element directory  = rootElt.element("directory");
                            if("1".equals(job.getJobType())){
                                directory.setText("/前置机流程/业务流程/公共组件/" + sjjflmc + "/" + tbName + "");
                            }else{
                                directory.setText("/中心端流程/业务流程/公共组件/" + sjjflmc + "/" + tbName + "");
                            }
                            Element entries = rootElt.element("entries");
                            List<Element> listEnry  = entries.elements("entry");
                            for(Element e:listEnry){
                                Element trName = e.element("name");
                                Element trDirectory = e.element("directory");
                                if("设置状态'1'".equals(trName.getText())||"设置状态'2'".equals(trName.getText()))
                                {
                                    if("1".equals(job.getJobType())) {
                                        trDirectory.setText("/前置机流程/业务流程/公共组件/" + sjjflmc + "/公用状态位");
                                    }else{
                                        trDirectory.setText("/中心端流程/业务流程/公共组件/" + sjjflmc + "/公用状态位");
                                    }
                                }

                                if("设置变量".equals(trName.getText())){
                                    Element fields = e.element("fields");
                                    Element field = fields.element("field");
                                    Element variable_value =field.element("variable_value");
                                    variable_value.setText(tbName);
                                }

                                if(trName.getText().indexOf("数据上传")>-1){
                                    trName.setText(tbName+"数据上传");
                                    Element transname = e.element("transname");
                                    transname.setText(tbName);
                                    if("1".equals(job.getJobType())){
                                        trDirectory.setText("/前置机流程/业务流程/公共组件/" + sjjflmc + "/" + tbName + "");
                                    }else{
                                        trDirectory.setText("/中心端流程/业务流程/公共组件/" + sjjflmc + "/" + tbName + "");
                                    }
                                }
                            }

                            //设置HOPS
                            Element hops = rootElt.element("hops");
                            List<Element> listHop  = hops.elements("hop");
                            for(Element el:listHop){
                                Element from = el.element("from");
                                if(from.getText().indexOf("数据上传")>=0){
                                    from.setText(tbName+"数据上传");
                                }
                                Element to = el.element("to");
                                if(to.getText().indexOf("数据上传")>=0){
                                    to.setText(tbName+"数据上传");
                                }
                            }
                            job.setJobXml(document.asXML());
                        } catch (DocumentException e) {
                            e.printStackTrace();
                        }
                    }
                    xmglDao.insertJob(jobList);
                }

                //生成tran信息
                //根据表名获取数据元信息
                List<SjyEntity> listSjy= xmglDao.queryXmbzSjy(xmdm,tbName);
                //根据表名获取tran
                List<TranEntity> tranList = xmglDao.queryTranByName(tbName);
                //存在则更新
                if(tranList!=null && tranList.size()>0){
                    for (TranEntity tr:tranList) {
                        tr.setStates("0");
                        //解析XML，修改step
                        String xml = tr.getTranXml();
                        try {
                            Document document = DocumentHelper.parseText(xml); // 将字符串转为XML
                            Element rootElt = document.getRootElement(); // 获取根节点

                            List<Element> listStep  = rootElt.elements("step");
                            for(Element step:listStep){
                                Element name =step.element("name");
                                if("异常处理".equals(name.getText())){
                                    Element sql =step.element("sql");
                                    String sqlText="UPDATE "+tbName+" set ${SCZT} = '3' WHERE  ";
                                    for(SjyEntity sjy:listSjy){
                                        if("1".equals(sjy.getSfzj()))
                                            sqlText = sqlText+sjy.getSjkzdm()+"= '?' AND";
                                    }
                                    sqlText = sqlText.substring(1,sqlText.length()-4);
                                    sql.setText(sqlText);
                                }

                                if("表输入".equals(name.getText())){
                                    Element sql =step.element("sql");
                                    String sqlText="SELECT";
                                    for(SjyEntity sjy:listSjy){
                                        sqlText = sqlText+sjy.getSjkzdm()+",";
                                    }
                                    sqlText = sqlText.substring(1,sqlText.length()-1);
                                    sqlText = sqlText+" FROM "+tbName+" where ${SCZT} = '1' ";
                                    sql.setText(sqlText);
                                }

                                if("表输出".equals(name.getText())){
                                    Element fields =step.element("fields");
                                    List<Element> listField  = fields.elements("field");
                                    for (Element field:listField) {
                                        fields.remove(field);
                                    }

                                    //动态创建表输出的列
                                    for(SjyEntity sjy:listSjy){
                                        String colName = sjy.getSjkzdm();
                                        Element field = DocumentHelper.createElement("field");
                                        Element column_name = DocumentHelper.createElement("column_name");
                                        column_name.setText(colName);
                                        field.add(column_name);
                                        Element stream_name = DocumentHelper.createElement("stream_name");
                                        stream_name.setText(colName);
                                        field.add(stream_name);
                                        listField.add(field);
                                    }
                                }
                            }
                            tr.setTranXml(document.asXML());
                        }catch (DocumentException e){
                            e.printStackTrace();
                        }
                    }
                    xmglDao.updateTran(tranList);
                }else {
                    //不存在则新增,先复制一个，再修改
                    List<TranEntity> tranListCopy =  xmglDao.queryOneTran();
                    for(TranEntity tran : tranListCopy){
                        //修改复制后的新tran
                        tran.setTranName(tbName + ".ktr");
                        if("1".equals(tran.getTranType())){
                            tran.setTranPath("前置机流程\\业务流程\\公共组件\\" + sjjflmc + "\\" + tbName + "\\");
                        }else{
                            tran.setTranPath("中心端流程\\业务流程\\公共组件\\" + sjjflmc + "\\" + tbName + "\\");
                        }
                        String xml = tran.getTranXml();

                        //解析xml，并更新为新表的XML
                        try {
                            Document document = DocumentHelper.parseText(xml); // 将字符串转为XML
                            Element rootElt = document.getRootElement(); // 获取根节点
                            Element info = rootElt.element("info");
                            Element name = info.element("name");
                            name.setText(tbName);
                            Element directory  = info.element("directory");
                            if("1".equals(tran.getTranType())){
                                directory.setText("/前置机流程/业务流程/公共组件/" + sjjflmc + "/" + tbName + "");
                            }else{
                                directory.setText("/中心端流程/业务流程/公共组件/" + sjjflmc + "/" + tbName + "");
                            }

                            List<Element> listStep  = rootElt.elements("step");
                            for(Element step:listStep){
                                Element nameStep =step.element("name");
                                if("异常处理".equals(nameStep.getText())){
                                    Element sql =step.element("sql");
                                    String sqlText="UPDATE "+tbName+" set ${SCZT} = '3' WHERE  ";
                                    for(SjyEntity sjy:listSjy){
                                        if("1".equals(sjy.getSfzj()))
                                            sqlText = sqlText+sjy.getSjkzdm()+"= '?' AND";
                                    }
                                    sqlText = sqlText.substring(1,sqlText.length()-4);
                                    sql.setText(sqlText);
                                }

                                if("表输入".equals(nameStep.getText())){
                                    Element sql =step.element("sql");
                                    String sqlText="SELECT";
                                    for(SjyEntity sjy:listSjy){
                                        sqlText = sqlText+sjy.getSjkzdm()+",";
                                    }
                                    sqlText = sqlText.substring(1,sqlText.length()-1);
                                    sqlText = sqlText+" FROM "+tbName+" where ${SCZT} = '1' ";
                                    sql.setText(sqlText);
                                }

                                if("表输出".equals(nameStep.getText())){
                                    Element fields =step.element("fields");
                                    List<Element> listField  = fields.elements("field");
                                    for (Element field:listField) {
                                        fields.remove(field);
                                    }

                                    //动态创建表输出的列
                                    for(SjyEntity sjy:listSjy){
                                        String colName = sjy.getSjkzdm();
                                        Element field = DocumentHelper.createElement("field");
                                        Element column_name = DocumentHelper.createElement("column_name");
                                        column_name.setText(colName);
                                        field.add(column_name);
                                        Element stream_name = DocumentHelper.createElement("stream_name");
                                        stream_name.setText(colName);
                                        field.add(stream_name);
                                        listField.add(field);
                                    }
                                }
                            }
                            tran.setTranXml(document.asXML());
                        } catch (DocumentException e) {
                            e.printStackTrace();
                        }
                    }
                    xmglDao.insertTran(tranListCopy);
                }
            }
        }

        /****************以下是生成ETL文件部分**********************************/
        //遍历生成jobjob和tran的配置数据
        //获取tran和job的配置数据
        List<JobEntity> listJob1 = xmglDao.queryJob();
        List<JobEntity> listJob2 = xmglDao.queryTran();
        listJob1.addAll(listJob2);

        for (JobEntity job : listJob1) {
            try {
                String jobPath = job.getJobPath();
                File directory = new File("");// 参数为空
                String courseFile = directory.getCanonicalPath();  //获取根目录
                String filePath = courseFile+"\\kettle-repository\\"+jobPath;  //文件夹路径
                if (!Files.exists(Paths.get(filePath))) {
                    Files.createDirectories(Paths.get(filePath));
                }
                String jobxml = job.getJobXml();
                String jobName = job.getJobName();
                File file = new File(filePath + jobName);
                //如果文件不存在，则创建新的文件
                if (!file.exists()) {
                    file.createNewFile();
                }
                //写入内容到文件里
                writeFileContent(filePath + jobName, jobxml);
            } catch (IOException e) {
                e.printStackTrace();
                return "0";
            }
        }
        return "1";
    }


    /**
     * 向文件中写入内容
     *
     * @param filepath 文件路径与名称
     * @param newstr   写入的内容
     * @return
     * @throws IOException
     */
    public static boolean writeFileContent(String filepath, String newstr) throws IOException {
        Boolean bool = false;
        String filein = newstr + "\r\n";//新写入的行，换行
        String temp = "";

        FileInputStream fis = null;
        InputStreamReader isr = null;
        BufferedReader br = null;
        FileOutputStream fos = null;
        PrintWriter pw = null;
        try {
            File file = new File(filepath);//文件路径(包括文件名称)
            //将文件读入输入流
            fis = new FileInputStream(file);
            isr = new InputStreamReader(fis);
            br = new BufferedReader(isr);
            StringBuffer buffer = new StringBuffer();
            buffer.append(filein);

            fos = new FileOutputStream(file);
            pw = new PrintWriter(fos);
            pw.write(buffer.toString().toCharArray());
            pw.flush();
            bool = true;
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            //不要忘记关闭
            if (pw != null) {
                pw.close();
            }
            if (fos != null) {
                fos.close();
            }
            if (br != null) {
                br.close();
            }
            if (isr != null) {
                isr.close();
            }
            if (fis != null) {
                fis.close();
            }
        }
        return bool;
    }
}
